/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DataAccess;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.table.TableModel;

/**
 *
 * @author thangvv0907i
 */
public class InventoryManagerFunction_DA {

    public int getTotalCate_DA() {
        int totalCate=0;
        ResultSet rs=null;
        try {
            CallableStatement getTotalCate = ConnectionDB.getConnection().prepareCall("{call sp_getTotalCate}");
            rs = getTotalCate.executeQuery();
            while(rs.next())
            {
                totalCate = rs.getInt(1);
            }
        } catch (SQLException ex) {
           ex.printStackTrace();
        }
        return totalCate;
    }

    public int getTotalItems_DA() {
        int totalItems=0;
        ResultSet rs=null;
        try {
            CallableStatement getTotalItems = ConnectionDB.getConnection().prepareCall("{call sp_getTotalItems}");
            rs = getTotalItems.executeQuery();
            while(rs.next())
            {
                totalItems = rs.getInt(1);
            }
        } catch (SQLException ex) {
           ex.printStackTrace();
        }
        return totalItems;
    }

    public TableModel getItemTableValue()
    {
        SetTableModel rtm = new SetTableModel();
        String Query = "select * from Items where I_Status=1";
        try {
            rtm.setHostURL(ConnectionDB.getConnectionString());
        } catch (Exception ex) {
            Logger.getLogger(AdminFunction.class.getName()).log(Level.SEVERE, null, ex);
        }
        rtm.setQuery(Query);
        return rtm;
    }

    public int updateQuantity_DA(int Quantity,String ItemID) {
        int respond=0;
        try {
            CallableStatement updateQuantity = ConnectionDB.getConnection().prepareCall("{call sp_updateQuantity(?,?)}");
            updateQuantity.setInt(2, Quantity);
            updateQuantity.setString(1, ItemID);
            respond = updateQuantity.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return respond;
    }

    public int ViewItemNotAvailable() {
       int Items=0;
        ResultSet rs=null;
        try {
            CallableStatement getTotalItems = ConnectionDB.getConnection().prepareCall("{call sp_ItemNot}");
            rs = getTotalItems.executeQuery();
            while(rs.next())
            {
                Items = rs.getInt(1);
            }
        } catch (SQLException ex) {
           ex.printStackTrace();
        }
        return Items;
    }

    public TableModel getItemNot() {
        SetTableModel rtm = new SetTableModel();
        String Query = "select * from Items where I_Quantity <= 0 and I_Status=1";
        try {
            rtm.setHostURL(ConnectionDB.getConnectionString());
        } catch (Exception ex) {
            Logger.getLogger(AdminFunction.class.getName()).log(Level.SEVERE, null, ex);
        }
        rtm.setQuery(Query);
        return rtm;
    }

    public float getTotalPrice() {
        float TotalPrice=0;
        ResultSet rs=null;
        try {
            CallableStatement getTotalItems = ConnectionDB.getConnection().prepareCall("{call TotalPrice}");
            rs = getTotalItems.executeQuery();
            while(rs.next())
            {
                TotalPrice = rs.getFloat(1);
            }
        } catch (SQLException ex) {
           ex.printStackTrace();
        }
        return TotalPrice;
    }

    public int getTotalItemSold() {
        int TotalItemSold=0;
        ResultSet rs=null;
        try {
            CallableStatement getTotalItems = ConnectionDB.getConnection().prepareCall("{call ItemSold}");
            rs = getTotalItems.executeQuery();
            while(rs.next())
            {
                TotalItemSold = rs.getInt(1);
            }
        } catch (SQLException ex) {
           ex.printStackTrace();
        }
        return TotalItemSold;
    }
    
    public TableModel getItemsSold() {
        SetTableModel rtm = new SetTableModel();
        String Query = "select BD.B_ID,BD.I_ID,BD.Quantity,BD.Price,B.U_ID,B.CustID,B.Date,B.Discount,B.Payment,B.Total from BillDetails as BD inner join Bills as B on BD.B_ID=B.B_ID where datepart(mm,B.Date)=datepart(mm,getDate())";
        try {
            rtm.setHostURL(ConnectionDB.getConnectionString());
        } catch (Exception ex) {
            Logger.getLogger(AdminFunction.class.getName()).log(Level.SEVERE, null, ex);
        }
        rtm.setQuery(Query);
        return rtm;
    }

    public Vector getCate(int Index) {
        Vector cate=new Vector();
        ResultSet rs=null;
        try {
            CallableStatement smt = ConnectionDB.getConnection().prepareCall("{call getcategories(?)}");
            smt.setInt(1, Index);
            rs = smt.executeQuery();
            while(rs.next())
            {
                cate.addElement(rs.getString("C_Name"));
            }
        } catch (SQLException ex) {
           ex.printStackTrace();
        }
        return cate;
    }

    public TableModel getItemCate(String CName,int month)
    {
        int CID = getCateID(CName);
        SetTableModel rtm = new SetTableModel();
        String Query = "select I.I_ID,I.I_Name,I.C_ID,I.I_Quantity,I.I_Price,I.I_Unit,B.Total from Items as I inner join BillDetails as BD on I.I_ID=BD.I_ID inner join Bills as B on B.B_ID=BD.B_ID where  I.C_ID="+CID+" and (DateDiff(mm,B.Date,getDate())="+month+")";
        try {
            rtm.setHostURL(ConnectionDB.getConnectionString());
        } catch (Exception ex) {
            Logger.getLogger(AdminFunction.class.getName()).log(Level.SEVERE, null, ex);
        }
        rtm.setQuery(Query);
        return rtm;
    }

    public int getCateID(String CateName)
    {
        int CateID=0;

        ResultSet rs=null;
        try {
            CallableStatement getTotalItems = ConnectionDB.getConnection().prepareCall("{call getCateID(?)}");
            getTotalItems.setString(1, CateName);
            rs = getTotalItems.executeQuery();
            while(rs.next())
            {
                CateID = rs.getInt(1);
            }
        } catch (SQLException ex) {
           ex.printStackTrace();
        }
        return CateID;
    }
}
